﻿using System;
using System.Linq;
using CreatingObject;

namespace SearchingSales
{
    class SearchingSales
    {
        static void Main()
        {
            string country = "Canada";
            int year = 1997;
            NorthwindEntities nWEntity = new NorthwindEntities();

            FindingCustomers(country, year, nWEntity);

            NativeFindingCustomers(country, year, nWEntity);

            string region = "SP";
            DateTime startDate = new DateTime(1997, 2, 1);
            DateTime endDate = new DateTime(1998, 1, 1);
            FindingSalesByRegionAndDatePeriod(nWEntity, region, startDate, endDate);
        }

        private static void FindingSalesByRegionAndDatePeriod(NorthwindEntities nWEntity, string region, DateTime startDate, DateTime endDate)
        {
            var sales =
                from o in nWEntity.Orders
                where o.ShipRegion == region && o.ShippedDate > startDate && o.ShippedDate < endDate
                select o.OrderID;
            foreach (var sale in sales)
            {
                Console.WriteLine(sale);
            }
        }

        private static void NativeFindingCustomers(string country, int year, NorthwindEntities nWEntity)
        {
            string nativeSQLQuery =
              "SELECT c.ContactName " +
              "FROM dbo.Orders o " +
              "JOIN dbo.Customers c " +
              "ON o.CustomerID=c.CustomerID " +
              "WHERE o.ShipCountry = {0} AND YEAR(o.ShippedDate) = {1}";
            object[] parameters = { country, year };
            var employees = nWEntity.ExecuteStoreQuery<string>(nativeSQLQuery, parameters);
            foreach (var emp in employees)
            {
                Console.WriteLine(emp);
            }
        }

        private static void FindingCustomers(string country, int year, NorthwindEntities nWEntity)
        {
            var customers =
                from o in nWEntity.Orders
                where o.ShipCountry == country && o.ShippedDate.Value.Year == year
                select o.Customer;
            foreach (var item in customers)
            {
                Console.WriteLine(item.ContactName);
            }
        }
    }
}
